import requests
import json
import base64
import os
import subprocess
import time
from sqlalchemy import create_engine, Column, Integer, String, ForeignKey
# from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker, relationship
from sqlalchemy.types import JSON 
from sqlalchemy import Column, String
from sqlalchemy import Column, Integer, String, MetaData
from sqlalchemy.orm import declarative_base
from flask import Flask, jsonify, request
from flask_cors import CORS

# 创建数据库引擎（使用SQLite）
db = "sqlite:///DataBase/Main1.db"
engine = create_engine(db)


# 创建一个基本的ORM类
Base = declarative_base()

# 第一张表格：课程和视频关联
class CourseVideo(Base):
    
    __tablename__ = 'course_videos'

    id = Column(Integer, primary_key=True, autoincrement=True)
    course_id = Column(String, ForeignKey('courses.id'), nullable=False)
    video_id = Column(String, ForeignKey('videos.id'), nullable=False)

# 第二张表格：视频详细信息
class Video(Base):
    __tablename__ = 'videos'

    id = Column(Integer, primary_key=True,autoincrement=True)
    title = Column(String)
    video_id = Column(String, unique=True, nullable=False)
    node_json = Column(JSON)
    edge_json = Column(JSON)
    tree_json = Column(JSON)

    # 建立反向关系，用于查询课程关联的视频
    courses = relationship('Course', secondary='course_videos')

# 第三张表格：课程信息
class Course(Base):
    __tablename__ = 'courses'

    id = Column(Integer, primary_key=True,autoincrement=True)
    course_id = Column(String, unique=True, nullable=False)
    repository_url = Column(String)
    # 可以添加其他课程信息字段

class User(Base):
    __tablename__ = 'users'

    student_id = Column(Integer, primary_key=True)  # 学号作为主键
    pwd = Column(String)  # 密码
    access_key = Column(String)  # 访问密钥
    email = Column(String)  # 学生邮箱

class Phrase(Base):
    __tablename__ = 'phrases'

    id = Column(Integer, primary_key=True,autoincrement=True)
    text = Column(String(10))
    tag = Column(Integer, default=0)
    url_list = Column(JSON, default=list)

def search_phrase(tar_word):
    Session=sessionmaker(bind=engine)
    session=Session()
    record = session.query(Phrase).filter_by(text=tar_word, tag=1).first()

    if record:
        # print(record.url_list)
        return_list=record.url_list
        session.close()
        return return_list
    else:
        session.close()
        return None
    
def get_video_info_by_url(url):
    Session=sessionmaker(bind=engine)
    session=Session()
    try:
        video_info = session.query(Video).filter_by(video_id=url).first()

        if video_info:
            #print("successful")
            return video_info.title,video_info.tree_json
        else:
            #print("fail")
            return None,None
    except Exception as e:
        print(f"查询数据时出错：{str(e)}")
        return None,None
    

def ifStoryExist(student_id, course):
    path = f"/www/wwwroot/default/back/DataBase/GitStories/{student_id}/{course}"
    return os.path.exists(path)

#Linux Terminal run
def LNT(instruction):
    print(instruction)
    result = subprocess.run(instruction, shell=True, stdout=subprocess.PIPE, text=True)
    print(result.stdout)

def pushMdFileToShuishanGit(student_token,
                            course,
                            student_id,
                            md_file_name,
                            md_file_content,
                            story_url, 
                            student_email,
                            commit_message):
    repo_dir = f"/www/wwwroot/default/back/DataBase/GitStories/{student_id}/{course}"

    #if not exist,clone and init
    print("check if story exist ......")
    if not ifStoryExist(student_id, course):
        
        print("story not exist, clone and init ......")

        instructions_1 = [
            f"git clone {story_url} {repo_dir}",
            f"git -C {repo_dir} remote set-url origin https://{student_token}@gitea.shuishan.net.cn/10225501403/{course}",
            # f"git -C {repo_dir} branch {student_id}",
            f"git -C {repo_dir} checkout {student_id}",
            f"git -C {repo_dir} config --global --add push.default current",
        ]

        LNT(" && ".join(instructions_1))

        print("clone and init finished!")

    #write md file
    local_file_path = f"{repo_dir}/{md_file_name}"
    with open(local_file_path, "w") as f:
        f.write(md_file_content)
    f.close()
    print(f"write {md_file_name} finished!")

    #prepare to push
    with open(local_file_path, "r", encoding="utf-8") as file:
        content = file.read()
    # 将Markdown内容进行Base64编码
    content_base64 = base64.b64encode(content.encode("utf-8")).decode("utf-8")

    # 构建请求头
    headers = {
        "Authorization": f"token {student_token}",
        "Content-Type": "application/json",
    }

    # 构建请求体
    data = {
        "author": {
            "email": "10225501403@stu.ecnu.edu.cn",
            "name": "10225501403"
        },
        "committer": {
            "email": student_email,
            "name": student_id,
        },
        "content": content_base64,
        "message": commit_message,
        "branch": student_id,
    }

    # 发送POST请求
    url = f"https://gitea.shuishan.net.cn/api/v1/repos/10225501403/{course}/contents/{md_file_name}"
    response = requests.post(url, headers=headers, data=json.dumps(data))
    if response.status_code == 201:
        print(f"{student_id} successfully pushed {md_file_name} to {course} at {time.strftime('%Y-%m-%d %H:%M:%S',time.localtime(time.time()))}")
        return True
    else:
        print(f"{student_id} failed to push {md_file_name} to {course} at {time.strftime('%Y-%m-%d %H:%M:%S',time.localtime(time.time()))}")
        return False

def getStudentInformation(student_id):
    Session=sessionmaker(bind=engine)
    session=Session()
    user=session.query(User).filter_by(student_id=student_id).first()
    token=user.access_key
    email=user.email
    session.close()
    return token,email
def getCourseInformation(video_url):
    Session=sessionmaker(bind=engine)
    session=Session()
    video_course=session.query(CourseVideo).filter_by(video_id=video_url).first()
    course_id=video_course.course_id
    course=session.query(Course).filter_by(course_id=course_id).first()
    repository_url=course.repository_url
    course_name=course.course_id
    session.close()
    return course_name,repository_url

app = Flask(__name__)

CORS(app, resources={
    r"/test": {
        "origins": "*",
    }
})
@app.route('/test', methods=['POST','GET','OPTIONS'])
def test():
    return jsonify({"message": "success"})

CORS(app, resources={
    r"/pushMd": {
        "origins": "*",
    }
})
@app.route('/pushMd', methods=['POST','GET'])
def doPush():
    try: 
        print("flagtest_0")
        data = request.json
        student_id=data.get('student_id')
        video_url=data.get('url')
        md_file_name=data.get('md_file_name')
        md_file_content=data.get('md_file_content')
        commit_message=data.get('commit_message')
        print(f"Received POST request: {student_id},{video_url},{md_file_name},{md_file_content},{commit_message}")
        student_token,student_email=getStudentInformation(student_id)
        print("flagtest")
        course,story_url=getCourseInformation(video_url)
        print(f"Received POST request: {student_id},{video_url},{md_file_name},{md_file_content},{commit_message},{student_token},{student_email},{course},{story_url}")
        if pushMdFileToShuishanGit(student_token, course, student_id, md_file_name, md_file_content, story_url, student_email, commit_message):
            return jsonify({'success_flag': '200'})
        else:
            return jsonify({'success_flag': '201'})
    except Exception as e:
        print(e)
        return jsonify({"success_flag": "202"})

CORS(app, resources={
    r"/getAllJson": {
        "origins": "*",
    }
})
@app.route('/getAllJson', methods=['POST','GET'])
def postAllJson():
    try:
        Session=sessionmaker(bind=engine)
        session=Session()
        data=request.json
        url=data.get('url')
        print(f"Received POST request: {url}")
        video_node=session.query(Video).filter_by(video_id=url).first()
        if not video_node:
            print("No matching record found")
            return jsonify({"success_flag": "201"})
        else:
            node_json=video_node.node_json
            edge_json=video_node.edge_json
            tree_json=video_node.tree_json
            #print(node_json,edge_json,tree_json)
            return jsonify({"success_flag":"200","node_json":node_json,"edge_json":edge_json,"tree_json":tree_json})
        session.close()
    except Exception as e:
        print("try failed")
        return jsonify({"success_flag": "202"})

CORS(app, resources={
    r"/getLQTable": {
        "origins": "*",
    }
})
@app.route('/getLQTable', methods=['POST','GET'])
def postLQTable():
    try:
        data=request.json
        print(f"Received POST request: {data}")
        text_input=data.get('text_input')
        text_input=text_input.replace("\n","")
        Session=sessionmaker(bind=engine)
        session=Session()
        result_url_list = search_phrase(text_input)
        print("search result:",result_url_list)
        return_json={}
        for tar_url in result_url_list:
            title,tree_json = get_video_info_by_url(tar_url)
            if tree_json:
                return_json[title]=tree_json
            else:
                print(f"未找到与URL '{tar_url}' 相关的视频信息。")
        session.close()
        return jsonify(return_json)
    except Exception as e:
        print("try failed")
        return jsonify({"success_flag": "202"})

CORS(app, resources={
    r"/regist": {
        "origins": "*",
    }
})
@app.route('/regist', methods=['POST','GET'])
def add_user():
    data=request.json
    student_id=data.get('student_id')
    access_key=data.get('access_key')
    email=data.get('email')
    pwd=data.get('pwd')
    print("student_id:",student_id,"access_key:",access_key,"email:",email,"pwd:",pwd)
    Session=sessionmaker(bind=engine)
    session=Session()
    new_user = User(student_id=student_id, access_key=access_key, email=email, pwd=pwd)
    session.add(new_user)
    try:
        session.commit()
        print(f"用户 {student_id} 添加成功。")
        return jsonify({"success_flag": "200"})

    except Exception as e:
        session.rollback()
        print(f"添加用户失败: {e}")
        return jsonify({"success_flag": "201"})

CORS(app, resources={
    r"/logIn": {
        "origins": "*",
    }
})
@app.route('/logIn', methods=['POST'])
def logIn():
    try:
        data=request.json
        student_id=data.get('student_id')
        student_pwd=data.get('student_pwd')
        print("student_id:",student_id,"student_pwd:",student_pwd)
        Session=sessionmaker(bind=engine)
        session=Session()
        user=session.query(User).filter_by(student_id=student_id).first()
        if user:
            if user.pwd==student_pwd:
                print("user",user.student_id,"success login")
                session.close()
                return jsonify({"success_flag": "200"})
            else:
                print("user",user.student_id,"wrong password")
                session.close()
                return jsonify({"success_flag": "201"})
        else:
            print("user",user.student_id,"not exist")
            session.close()
            return jsonify({"success_flag": "202"})
    except Exception as e:
        print(e)
        return jsonify({"success_flag": "202"})

if __name__ == "__main__":
    
    ssl_cert = '/www/wwwroot/default/certificate.crt'
    ssl_key = '/www/wwwroot/default/private.key'
    
    app.run(host="0.0.0.0",port=8080,ssl_context=(ssl_cert, ssl_key),debug=False)